const express = require('express');
const mysql = require('mysql2/promise');
const cors = require('cors');
const app = express();
const port = 3001;

const dbConfig = {
  host: "localhost",
  user: "root",
  password: "YUsheng7989",
  database: "327test1",
  charset: 'utf8mb4'
};

app.use(express.json());
app.use(cors({
  origin: 'http://localhost:3000',
  methods: ['GET', 'POST', 'PUT', 'DELETE'],
  credentials: true
}));

const pool = mysql.createPool(dbConfig);
app.post('/class', async (req, res) => {
  const { 
    japanese = '',
    chinese = '',
    course = '',
    page = 1,
    limit = 10
  } = req.body;

  const offset = (page - 1) * limit;

  let connection;
  
  try {
    connection = await pool.getConnection();
    
    let whereClause = [];
    let queryParams = [];
    
    if (japanese) {
      whereClause.push('japanese LIKE ?');
      queryParams.push(`%${japanese}%`);
    }
    
    if (chinese) {
      whereClause.push('chinese LIKE ?');
      queryParams.push(`%${chinese}%`);
    }
    
    if (course) {
      whereClause.push('course LIKE ?');
      queryParams.push(`%${course}%`);
    }

    const whereSql = whereClause.length ? `WHERE ${whereClause.join(' AND ')}` : '';
    
    const [rows] = await connection.query(
      `SELECT * FROM class ${whereSql} ORDER BY id LIMIT ? OFFSET ?`,
      [...queryParams, parseInt(limit), parseInt(offset)]
    );
    
    const [countResult] = await connection.query(
      `SELECT COUNT(*) as total FROM class ${whereSql}`,
      queryParams
    );
    
    const total = countResult[0].total;
    
    res.json({
      success: true,
      data: rows,
      pagination: {
        total,
        page: parseInt(page),
        limit: parseInt(limit),
        pages: Math.ceil(total / limit)
      }
    });
  } catch (error) {
    console.error('查询错误:', error);
    res.status(500).json({
      success: false,
      message: '查询失败',
      error: error.message
    });
  } finally {
    if (connection) connection.release();
  }
});

app.post('/add', async (req, res) => {
  try {
    const { japanese, ruby, chinese, course } = req.body;
    const [maxIdResult] = await pool.query('SELECT MAX(id) as maxId FROM class');
    const nextId = (maxIdResult[0].maxId || 0) + 1;

    const [result] = await pool.query(
      'INSERT INTO class (id, japanese, ruby, chinese, course) VALUES (?, ?, ?, ?, ?)',
      [nextId, japanese, ruby, chinese, course]
    );
    res.status(201).json({
      success: true,
      id: nextId,
      message: '数据添加成功'
    });
  } catch (err) {
    res.status(500).json({
      success: false,
      error: err.message
    });
  }
});

app.post('/update', async (req, res) => {
  try {
    const { id, japanese, ruby, chinese, course } = req.body;
    await pool.query(
      'UPDATE class SET japanese = ?, ruby = ?, chinese = ?, course = ? WHERE id = ?',
      [japanese, ruby, chinese, course, id]
    );
    res.status(200).json({
      success: true,
      message: '数据修改成功'
    });
  } catch (err) {
    res.status(500).json({
      success: false,
      error: err.message
    });
  }
});

app.post('/delete', async (req, res) => {
  try {
    const { id } = req.body;
    await pool.query('DELETE FROM class WHERE id = ?', [id]);
    res.status(200).json({
      success: true,
      message: '数据删除成功'
    });
  } catch (err) {
    res.status(500).json({
      success: false,
      error: err.message
    });
  }
});
app.listen(port, () => {
  console.log(`服务器运行在 http://localhost:${port}`);
  // console.log(`POST查询接口: http://localhost:${port}/api/class`);
  // console.log('请求示例: POST 携带JSON参数 { "japanese": "日本", "chinese": "你好", "page": 1, "limit": 10 }');
});